/**
 * TabVD is a Virtual Desktop Client/Server designed specifically for connecting
 * from Tablet like devices.
 *
 * Copyright (C) 2011 Subhabrata Ghosh
 *
 * This program is free software: you can redistribute it and/or modify it under
 * the terms of the GNU General Public License as published by the Free Software
 * Foundation, either version 3 of the License, or (at your option) any later
 * version.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
 * details.
 *
 * You should have received a copy of the GNU General Public License along with
 * this program. If not, see <http://www.gnu.org/licenses/>.
 *
 * Schema.java
 *
 *  Created on: Jul 19, 2011
 *      Author: subhagho
 */
package net.langene.tabvd.core.db;

import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Date;
import java.util.Hashtable;
import java.util.LinkedList;
import java.util.List;

import net.langene.tabvd.core.DateUtils;

/**
 * @author subhagho
 * 
 */
public class Schema {
	public static enum DataType {
		String, Boolean, Short, Integer, Long, Double, Date, Timestamp
	}

	public static class Column {
		public String Name;
		public DataType Type = null;
		public int Size = -1;
		public boolean IsPrimaryKey = false;
		public boolean IsNullable = false;

		/**
		 * Convert DB Type String to DataType.
		 * 
		 * net.langene.tabvd.core.db.Column
		 * 
		 * @param dbtype
		 *            - DB Datatype String
		 * @return - DataType
		 * @throws Exception
		 */
		public static DataType getDataType(String dbtype) throws Exception {
			if (dbtype.compareToIgnoreCase("VARCHAR") == 0) {
				return DataType.String;
			} else if (dbtype.compareToIgnoreCase("BIT") == 0) {
				return DataType.Boolean;
			} else if (dbtype.compareToIgnoreCase("SMALLINT") == 0) {
				return DataType.Short;
			} else if (dbtype.compareToIgnoreCase("INTEGER") == 0) {
				return DataType.Integer;
			} else if (dbtype.compareToIgnoreCase("BIGINT") == 0) {
				return DataType.Long;
			} else if (dbtype.compareToIgnoreCase("DOUBLE") == 0) {
				return DataType.Double;
			} else if (dbtype.compareToIgnoreCase("DATE") == 0) {
				return DataType.Date;
			} else if (dbtype.compareToIgnoreCase("TIMESTAMP") == 0) {
				return DataType.Timestamp;
			}
			throw new Exception("Unsupported DB Type [" + dbtype + "]");
		}

		/**
		 * Convert DB Type to DataType.
		 * 
		 * net.langene.tabvd.core.db.Column
		 * 
		 * @param dbtype
		 * @return
		 * @throws Exception
		 */
		public static DataType getDataType(int dbtype) throws Exception {
			if (dbtype == Types.VARCHAR) {
				return DataType.String;
			} else if (dbtype == Types.BIT) {
				return DataType.Boolean;
			} else if (dbtype == Types.SMALLINT) {
				return DataType.Short;
			} else if (dbtype == Types.INTEGER) {
				return DataType.Integer;
			} else if (dbtype == Types.BIGINT) {
				return DataType.Long;
			} else if (dbtype == Types.DOUBLE) {
				return DataType.Double;
			} else if (dbtype == Types.DATE) {
				return DataType.Date;
			} else if (dbtype == Types.TIMESTAMP) {
				return DataType.Timestamp;
			}
			throw new Exception("Unsupported DB Type [" + dbtype + "]");
		}

		/**
		 * Convert DataType to SQL Types.
		 * 
		 * net.langene.tabvd.core.db.Column
		 * 
		 * @param type
		 *            - DataType
		 * @return - java.sql.Types constants.
		 * @throws Exception
		 */
		public static int getSqlType(DataType type) throws Exception {
			switch (type) {
			case String:
				return Types.VARCHAR;
			case Boolean:
				return Types.BIT;
			case Short:
				return Types.SMALLINT;
			case Integer:
				return Types.INTEGER;
			case Long:
				return Types.BIGINT;
			case Double:
				return Types.DOUBLE;
			case Date:
				return Types.DATE;
			case Timestamp:
				return Types.TIMESTAMP;
			default:
				throw new Exception("Unsupported DataType [" + type.toString()
						+ "]");
			}
		}

		public static String getSqlTypeString(DataType type) throws Exception {
			switch (type) {
			case String:
				return "VARCHAR";
			case Boolean:
				return "BIT";
			case Short:
				return "SMALLINT";
			case Integer:
				return "INTEGER";
			case Long:
				return "BIGINT";
			case Double:
				return "DOUBLE";
			case Date:
				return "DATE";
			case Timestamp:
				return "TIMESTAMP";
			default:
				throw new Exception("Unsupported DataType [" + type.toString()
						+ "]");
			}
		}

		public Object checkColumnValue(Object value) throws Exception {
			if (!IsNullable && value == null)
				throw new Exception("Column is not nullable.");
			if (value != null)
				return value;

			switch (Type) {
			case Boolean:
				return false;
			case Date:
				return DateUtils.now();
			case Double:
				return Double.MIN_VALUE;
			case Integer:
				return Integer.MIN_VALUE;
			case Long:
				return Long.MIN_VALUE;
			case Short:
				return Short.MIN_VALUE;
			case String:
				return "";
			case Timestamp:
				return DateUtils.now();
			}
			throw new Exception("Unsupported DataType [" + Type.toString()
					+ "]");
		}

		/**
		 * Set the current columns value for the prepared statement.
		 * 
		 * net.langene.tabvd.core.db.Column
		 * 
		 * @param pstmnt
		 *            - PreparedStatement.
		 * @param index
		 *            - Column Index
		 * @param value
		 *            - Column Value
		 * @throws Exception
		 */
		public void setPreparedParameter(PreparedStatement pstmnt, int index,
				Object value) throws Exception {
			value = checkColumnValue(value);
			switch (Type) {
			case Boolean:
				if (value instanceof Boolean) {
					pstmnt.setBoolean(index, (Boolean) value);
					return;
				}
				break;
			case Date:
				if (value instanceof Date) {
					java.sql.Date date = DBHelper.toSqlDate((Date) value);
					pstmnt.setDate(index, date);
					return;
				} else if (value instanceof java.sql.Date) {
					pstmnt.setDate(index, (java.sql.Date) value);
					return;
				}
				break;
			case Double:
				if (value instanceof Double) {
					pstmnt.setDouble(index, (Double) value);
					return;
				}
				break;
			case Integer:
				if (value instanceof Integer) {
					pstmnt.setInt(index, (Integer) value);
					return;
				}
				break;
			case Long:
				if (value instanceof Long) {
					pstmnt.setLong(index, (Long) value);
					return;
				}
				break;
			case Short:
				if (value instanceof Short) {
					pstmnt.setShort(index, (Short) value);
					return;
				}
				break;
			case String:
				pstmnt.setString(index, value.toString());
				break;
			case Timestamp:
				if (value instanceof Date) {
					Timestamp ts = DBHelper.toSqlTimestamp((Date) value);
					pstmnt.setTimestamp(index, ts);
					return;
				} else if (value instanceof Timestamp) {
					pstmnt.setTimestamp(index, (Timestamp) value);
					return;
				}
				break;
			}
			throw new Exception("Unsupported DataType [" + Type.toString()
					+ "]");
		}

		public String getColumnDef() throws Exception {
			StringBuffer buffer = new StringBuffer();
			buffer.append(Name).append(" ").append(getSqlTypeString(Type));
			if (Size > 0) {
				buffer.append("(").append(Size).append(")");
			}
			if (!IsNullable)
				buffer.append(" NOT NULL ");
			return buffer.toString();
		}

		/**
		 * Validate the Column definition.
		 * 
		 * net.langene.tabvd.core.db.Column
		 *
		 * @throws Exception
		 */
		public void validate() throws Exception {
			if (Name == null || Name.isEmpty())
				throw new Exception(
						"Invlaid Column definition : Name is null or empty.");
			if (Type == null)
				throw new Exception(
						"Invlaid Column definition : Type is null or empty.");
			if (IsPrimaryKey)
				IsNullable = false;
			if (Type == DataType.String)
				if (Size <= 0)
					throw new Exception(
							"Invlaid Column definition : Size is not valid for String.");

		}

		/*
		 * (non-Javadoc)
		 * 
		 * @see java.lang.Object#toString()
		 */
		@Override
		public String toString() {
			StringBuffer buffer = new StringBuffer();
			buffer.append(Name);
			buffer.append(" ").append(Type);
			if (Size > 0) {
				buffer.append("[").append(Size).append("]");
			}
			return buffer.toString();
		}

	}

	public static class Table {
		public String Name;
		public int CreateSequence;
		public List<Column> Columns = new LinkedList<Schema.Column>();
		public List<Column> KeyColumns = null;

		/**
		 * Get the Column object for the specified name.
		 * 
		 * net.langene.tabvd.core.db.Table
		 * 
		 * @param name
		 *            - Column name
		 * @return
		 */
		public Column getColumn(String name) {
			if (Columns.size() > 0) {
				for (Column column : Columns) {
					if (column.Name.compareToIgnoreCase(name) == 0)
						return column;
				}
			}
			return null;
		}

		/**
		 * Add a column as a (part) Primary Key.
		 * 
		 * net.langene.tabvd.core.db.Table
		 * 
		 * @param col
		 *            - Column Definition.
		 */
		public void addKeyColumn(Column col) {
			if (KeyColumns == null)
				KeyColumns = new LinkedList<Schema.Column>();
			col.IsNullable = false;
			col.IsPrimaryKey = true;
			KeyColumns.add(col);
		}

		/**
		 * Get the table create SQL statement.
		 * 
		 * net.langene.tabvd.core.db.Table
		 * 
		 * @return
		 * @throws Exception
		 */
		public String getCreateSql() throws Exception {
			StringBuffer buffer = new StringBuffer();
			buffer.append("CREATE TABLE ").append(Name).append(" (");
			boolean first = true;
			for (Column col : Columns) {
				if (first)
					first = false;
				else
					buffer.append(", ");
				buffer.append(col.getColumnDef());
			}
			if (KeyColumns != null && KeyColumns.size() > 0) {
				buffer.append(", CONSTRAINT ").append("PK_").append(Name);
				buffer.append(" PRIMARY KEY (");
				first = true;
				for (Column col : KeyColumns) {
					if (first)
						first = false;
					else
						buffer.append(", ");
					buffer.append(col.Name);
				}
				buffer.append(")");
			}
			buffer.append(")");
			return buffer.toString();
		}

		/**
		 * Get the table Drop SQL statement.
		 * 
		 * net.langene.tabvd.core.db.Table
		 * 
		 * @return
		 */
		public String getDropSql() {
			return "DROP TABLE IF EXISTS " + Name;
		}

		/**
		 * Get the select SQL statement for this table.
		 * 
		 * net.langene.tabvd.core.db.Table
		 * 
		 * @param where
		 * @return
		 */
		public String getSelectSql(List<Column> where) {
			StringBuffer buffer = new StringBuffer();

			buffer.append("SELECT ");
			boolean first = true;
			for (Column column : Columns) {
				if (first)
					first = false;
				else {
					buffer.append(", ");
				}
				buffer.append(column.Name);
			}
			buffer.append(" FROM ").append(Name);
			if (where != null) {
				buffer.append(" WHERE ");
				first = true;
				for (Column column : where) {
					if (first)
						first = false;
					else {
						buffer.append(" AND ");
					}
					buffer.append(column.Name).append(" = ?");
				}
			} else if (KeyColumns != null && KeyColumns.size() > 0) {
				buffer.append(" WHERE ");
				first = true;
				for (Column column : KeyColumns) {
					if (first)
						first = false;
					else {
						buffer.append(" AND ");
					}
					buffer.append(column.Name).append(" = ?");
				}
			}
			return buffer.toString();
		}

		/**
		 * Get the delete SQL statement for this table.
		 * 
		 * net.langene.tabvd.core.db.Table
		 * 
		 * @param where
		 * @return
		 */
		public String getDeleteSql(List<Column> where) {
			StringBuffer buffer = new StringBuffer();

			buffer.append("DELETE FROM ").append(Name);
			boolean first = true;

			if (where != null) {
				buffer.append(" WHERE ");
				first = true;
				for (Column column : where) {
					if (first)
						first = false;
					else {
						buffer.append(" AND ");
					}
					buffer.append(column.Name).append(" = ?");
				}
			} else if (KeyColumns != null && KeyColumns.size() > 0) {
				buffer.append(" WHERE ");
				first = true;
				for (Column column : KeyColumns) {
					if (first)
						first = false;
					else {
						buffer.append(" AND ");
					}
					buffer.append(column.Name).append(" = ?");
				}
			}
			return buffer.toString();
		}

		/**
		 * Get the update SQL statement for this table.
		 * 
		 * net.langene.tabvd.core.db.Table
		 * 
		 * @param where
		 * @return
		 */
		public String getUpdateSql(List<Column> where) {
			StringBuffer buffer = new StringBuffer();

			buffer.append("UPDATE ").append(Name).append(" SET ");
			boolean first = true;
			for (Column column : Columns) {
				if (column.IsPrimaryKey)
					continue;
				if (first)
					first = false;
				else {
					buffer.append(", ");
				}
				buffer.append(column.Name).append(" = ?");
			}
			if (where != null) {
				buffer.append(" WHERE ");
				first = true;
				for (Column column : where) {
					if (first)
						first = false;
					else {
						buffer.append(" AND ");
					}
					buffer.append(column.Name).append(" = ?");
				}
			} else if (KeyColumns != null && KeyColumns.size() > 0) {
				buffer.append(" WHERE ");
				first = true;
				for (Column column : KeyColumns) {
					if (first)
						first = false;
					else {
						buffer.append(" AND ");
					}
					buffer.append(column.Name).append(" = ?");
				}
			}
			return buffer.toString();
		}

		/**
		 * Get the insert SQL statement for this table.
		 * 
		 * net.langene.tabvd.core.db.Table
		 * 
		 * @return
		 */
		public String getInsertSql() {
			StringBuffer buffer = new StringBuffer();
			StringBuffer params = new StringBuffer();

			buffer.append("INSERT INTO ").append(Name).append(" (");
			boolean first = true;
			for (Column column : Columns) {
				if (first)
					first = false;
				else {
					buffer.append(", ");
					params.append(", ");
				}
				buffer.append(column.Name);
				params.append("?");
			}
			buffer.append(") VALUES (").append(params.toString()).append(")");
			return buffer.toString();
		}

		/*
		 * (non-Javadoc)
		 * 
		 * @see java.lang.Object#toString()
		 */
		@Override
		public String toString() {
			StringBuffer buffer = new StringBuffer();
			buffer.append("[TABLE:").append(Name).append(" [COLUMNS:");

			for (Column col : Columns) {
				buffer.append("{").append(col.toString()).append("}");
			}
			buffer.append("]]\n");
			return buffer.toString();
		}
	}

	public Hashtable<String, Table> Tables = new Hashtable<String, Schema.Table>();

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		StringBuffer buffer = new StringBuffer();
		buffer.append("(SCHEMA-->\n");
		for (Table table : Tables.values()) {
			buffer.append(table.toString());
		}
		buffer.append(")");
		return buffer.toString();
	}
}
